package com.k.util.excel;

import com.k.util.excel.anno.ExcelProperty;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.util.*;

/**
 * @author k
 */
public class ExcelUtils {

    private static final String COL_DELIMITER = String.valueOf((char) 30);

    public static Workbook createWorkbook() {
        Workbook wb = null;
        try {
            wb = new XSSFWorkbook();
        } catch (Exception e) {
            try {
                wb = new SXSSFWorkbook();
            } catch (Exception e2) {
                try {
                    wb = new HSSFWorkbook();
                } catch (Exception e3) {
                    e3.printStackTrace();
                }
            }
        }
        return wb;
    }
    public static void export(Workbook wb ,String destFile){
        try (FileOutputStream os = new FileOutputStream(destFile)) {
            wb.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static <T> Workbook createWorkbook(Class<T> cls, List<T> dataList) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        Row header = sheet.createRow(0);

        List<Field> fieldList = new ArrayList<>();
        Field[] fields = cls.getDeclaredFields();
        for (Field field : fields) {
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
            if (excelProperty != null) {
                //fieldList.add(field);
                fieldList.add(field);
            }
        }
        // 排序字段
        fieldList.sort(Comparator.comparingInt(f -> f.getAnnotation(ExcelProperty.class).index()));

        // title
        for (int i = 0; i < fieldList.size(); i++) {
            Field field = fieldList.get(i);
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
            header.createCell(i).setCellValue(excelProperty.value()[0]);
        }
        // data
        if (dataList != null && dataList.size() > 0) {
            for (int i = 0; i < dataList.size(); i++) {
                Row dataRow = sheet.createRow(i + 1);
                T obj = dataList.get(i);
                for (int j = 0; j < fieldList.size(); j++) {
                    Field field = fieldList.get(j);
                    field.setAccessible(true);
                    try {
                        dataRow.createCell(j).setCellValue(String.valueOf(field.get(obj)));
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return workbook;
    }

    private static String getCellValue(Cell cell) {
        String v;
        if (cell == null) {
            return "";
        }
        CellType cellType = cell.getCellType();
        if (CellType.NUMERIC.equals(cellType)) {
            cell.setCellType(CellType.STRING);
        } else if (CellType.BOOLEAN.equals(cellType)) {
            cell.setCellType(CellType.STRING);
        } else if (CellType.FORMULA.equals(cellType)) {
            cell.setCellType(CellType.STRING);
        } else if (CellType.ERROR.equals(cellType)) {
            cell.setCellType(CellType.STRING);
        } else if (CellType._NONE.equals(cellType)) {
            cell.setCellType(CellType.STRING);
        } else {
            cell.setCellType(CellType.STRING);
        }
        v = cell.getStringCellValue();
        return v;
    }

    /**
     * Map 与 Excel 对应 sheet row col
     *
     * @param workbook workbook
     * @return Map
     */
    private static Map<Integer, Map<Integer, String>> readWorkbookToMap(Workbook workbook, int sheetNum) {
        Map<Integer, Map<Integer, String>> sheetMap = new HashMap<>();
        int sheetIndex = 0;
        int rowIndex = 0;
        // Sheet数
        for (int i = 0; i < sheetNum; i++) {
            int errSheet = i + 1;
            Sheet sheet = workbook.getSheetAt(i);
            int lastRowNum = sheet.getLastRowNum();
            Map<Integer, String> rowMap = new HashMap<>();
            // 行数
            for (int j = 0; j <= lastRowNum; j++) {
                StringJoiner lineJoiner = new StringJoiner(COL_DELIMITER);
                Row row = sheet.getRow(j);
                if (row == null) {
                    rowMap.put((sheetIndex + 1), null);
                    continue;
                }
                int lastColNum = row.getLastCellNum();
                // 列数
                for (int k = 0; k < lastColNum; k++) {
                    Cell cell = row.getCell(k);
                    String v = getCellValue(cell);
                    lineJoiner.add(v);
                }
                rowMap.put((rowIndex + 1), lineJoiner.toString());
            }
            sheetMap.put(errSheet, rowMap);
        }
        return sheetMap;
    }

    private static Map<Integer, Map<Integer, String>> readWorkbookToMap(Workbook workbook) {
        return readWorkbookToMap(workbook, 1);
    }

    private static String errMsg(int sheetNo, int rowNo, int colNo, String msg) {
        return String.format("sheet:%d row:%d col:%d msg:%s", sheetNo, rowNo, colNo, msg);
    }

    public static Map<Integer, Map<Integer, String>> readExcelToMap(InputStream is) throws IOException {
        Workbook workbook = WorkbookFactory.create(is);
        return readWorkbookToMap(workbook);
    }

    public static Map<Integer, Map<Integer, String>> readExcelToMap(File file) throws IOException {
        Workbook workbook = WorkbookFactory.create(file);
        return readWorkbookToMap(workbook);
    }

    public static Map<Integer, Map<Integer, String>> readExcelToMap(String filePathname) throws IOException {
        Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(filePathname)));
        return readWorkbookToMap(workbook);
    }

    public static Map<Integer, Map<Integer, String>> readExcelToMap(String filePathname, int sheetNo) throws IOException {
        Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(filePathname)));
        return readWorkbookToMap(workbook, sheetNo);
    }

    public static Workbook createTemplateExcel() throws IOException {
        Workbook workbook = WorkbookFactory.create(true);
        int sheetLength = 1;
        int rowLength = 2;
        int columnLength = 10;
        for (int i = 0; i < sheetLength; i++) {
            Sheet sheet = workbook.createSheet("sheet" + (i + 1));
            for (int j = 0; j < rowLength; j++) {
                Row row = sheet.createRow(j);
                for (int k = 0; k < columnLength; k++) {
                    Cell cell = row.createCell(k);
                    if (j == 0) {
                        cell.setCellValue("title" + (k + 1));
                    } else {
                        cell.setCellValue("data" + (k + 1));
                    }
                }
            }
            sheet.autoSizeColumn(0);
        }
        return workbook;
    }

    public static Workbook createTemplateExcelForMerge() throws IOException {
        Workbook workbook = WorkbookFactory.create(true);
        int sheetLength = 1;
        int rowLength = 2;
        int columnLength = 10;

        for (int i = 0; i < sheetLength; i++) {
            Sheet sheet = workbook.createSheet("sheet" + (i + 1));
            // title
            int rowIndexHeadStart = 0;
            int rowIndexHeadEnd = 1;
            for (int tRowIndex = rowIndexHeadStart; tRowIndex <= rowIndexHeadEnd; tRowIndex++) {
                Row row = sheet.createRow(tRowIndex);
                for (int tColumnIndex = 0; tColumnIndex < columnLength; tColumnIndex++) {
                    Cell cellTitle = row.createCell(tColumnIndex);
                    cellTitle.setCellValue("title-" + (tRowIndex + 1) + "-" + (tColumnIndex + 1));
                }
            }
            // data
            int rowLengthData = 10;
            int rowIndexData = rowIndexHeadEnd + 1;
            for (int j = rowIndexData; j < rowLengthData; j++) {
                Row row = sheet.createRow(j);
                for (int k = 0; k < columnLength; k++) {
                    Cell cell = row.createCell(k);
                    cell.setCellValue("data" + (k + 1));
                }
            }
            sheet.autoSizeColumn(0);
        }
        return workbook;
    }

    public static void writeExcelToDisk(List<String[]> titles, List<List> data) throws IOException {
        Workbook wb = WorkbookFactory.create(true);
        for (int i = 0; i < titles.size(); i++) {
            int sheetNo = i + 1;
            // sheet
            Sheet sheet = wb.createSheet("sheet" + sheetNo);
            Row row = sheet.createRow(0);

            // title
            String[] titleArr = titles.get(i);
            for (int j = 0; j < titleArr.length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(titleArr[j]);
            }

            // data

            // export name
            String pathName = "c:/test/xxt52.xlsx";
            try (FileOutputStream os = new FileOutputStream(pathName)) {
                wb.write(os);
            }
        }
    }

    public static void writeExcelToDisk() {
        Workbook wb = null;
        try {
//            wb = createTemplateExcel();
            wb = createTemplateExcelForMerge();
            String pathName = "e:/test/writeExcelToDisk1.xlsx";
            try (FileOutputStream os = new FileOutputStream(pathName)) {
                wb.write(os);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * Windows下，println() 等价于 print("\r\n")
     * Linux下，println()等价于print("\n")
     * Mac下，println()等价于print("\r")
     *
     * @param rowNo rowNo
     * @param arr   colArr
     */
    private static void printRow(Integer rowNo, String[] arr) {
        String indexF = String.format("%3s| ", rowNo + "行");
        String colJoinF = formatArr(arr);
        System.out.println(indexF + colJoinF);
    }

    private static void printSheetMap(Map<Integer, Map<Integer, String>> map) {
        for (Map.Entry<Integer, Map<Integer, String>> entry : map.entrySet()) {
            Integer sheetNo = entry.getKey();
            System.out.println("sheetNo:" + sheetNo);
            Map<Integer, String> sheetValue = entry.getValue();
            for (Map.Entry<Integer, String> entry1 : sheetValue.entrySet()) {
                Integer rowNo = entry1.getKey();
                String rowValue = entry1.getValue();
                if (rowValue == null) {
                    continue;
                }
                String[] rowArr = rowValue.split(COL_DELIMITER);
                printRow(rowNo, rowArr);
            }
            System.out.println("=============================================");
        }
    }

    private static String formatArr(String[] arr) {
        StringJoiner lineJoiner = new StringJoiner("|");
        int colWidth = 27;
        String align = "left";
        if (arr == null) {
            lineJoiner.add(String.format("%" + colWidth + "s", null));
        } else {
            for (int i = 0; i < arr.length; i++) {
                int colIndex = i;
                int colNo = colIndex + 1;
                lineJoiner.add(String.format("%" + colWidth + "s", arr[i] + "(" + colNo + "列)"));

            }
        }
        return lineJoiner.toString();
    }

    private static String arrJoin(String[] arr) {
        return (arr == null) ? null : String.join("|", arr);
    }

    private static String[] arrUpercaselettersAscii(int n) {
        String[] arr = new String[n];
        for (int i = 0; i < n; i++) {
            int ascii = i + 65;
            char a = (char) ascii;
            arr[i] = String.valueOf(a) + (i + 1);
        }
        return arr;
    }

    public static String getArrSafeValue(String[] arr, int index) {
        try {
            return arr[index];
        } catch (IndexOutOfBoundsException e) {
            return null;
        }
    }

    public static void mapToDb(Map<Integer, Map<Integer, String>> map) {
        // 遍历 Sheet
        for (Map.Entry<Integer, Map<Integer, String>> entry : map.entrySet()) {
            Integer sheetNum = entry.getKey();
            Map<Integer, String> sheetValue = entry.getValue();
            System.out.println("sheetNum:" + sheetNum);
            for (Map.Entry<Integer, String> entry1 : sheetValue.entrySet()) {
                Integer rowNo = entry1.getKey();
                String rowValue = entry1.getValue();
                if (rowValue == null) {
                    printRow(rowNo, null);
                    continue;
                }
                if (rowNo == 1) {
                    String[] colTitle = rowValue.split(COL_DELIMITER);
                    System.out.println(String.format("%3s|", " ") + formatArr(arrUpercaselettersAscii(colTitle.length)));
                    // title
                    printRow(rowNo, colTitle);
                } else {
                    String[] colArr = rowValue.split(COL_DELIMITER);
                    printRow(rowNo, colArr);
                    // TODO 1.Validator
                    String col1 = getArrSafeValue(colArr, 0);
                    String col2 = getArrSafeValue(colArr, 1);
                    String col16 = getArrSafeValue(colArr, 16);
                    // TODO 2. insert db
                }
            }
        }
    }


    public static void main(String[] args) {
        //"c:/test/xxt.xlsx";
        String filePath = "C:/cdss/Dispatcher-im.xls";
        Map<Integer, Map<Integer, String>> map;
        try {
            // 1.读取
            map = readExcelToMap(filePath);
            printSheetMap(map);
            writeExcelToDisk();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
